﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using DAL;
using CommontLibrary;
using System.Xml;
using System.Xml.Xsl;
public partial class UserControl_Reports_loadReport1b : System.Web.UI.UserControl
{
    #region "Member variables"
        string[] strTruongHocs = { "Nhận L1", "Không liên lạc được", "Ko đúng đối tượng", "Ở vùng khác", "Lên được L2 (sau gọi điện)" };
        string[] strColumn = { "L1Nhan", "KhongLienLac", "KhongDungDoiTuong", "VungKhac", "L2Len" };
        int[] intTotalMothThuTrong;
        int[] intTotalMothBanGiao;
        int[] intTotalMothNghiemThu;
        DAReport objReport;
    #endregion
    #region "Methods"
    private void loadDataAccess()
    {
        SqlConnection sqlConnec = ConnectData.getConnection();
        objReport = new DAReport(sqlConnec);
    }
    public void loadNgayView(ref string strTable, string strNameDay, string strText)
    {
        string strTotal = "\n<td style='font-weight:normal;width:41pt;background-color:#800000;color:#ffffff;'>Tổng</td>";
        if (strNameDay == "SATURDAY")
        {
            strTable += "\n<td align='center' style='font-weight:normal;width:41px;background-color:Yellow;color:#000000;' >Th7 " + strText + " </td>";
        }
        else if (strNameDay == "SUNDAY")
        {
            strTable += "\n<td align='center' style='font-weight:normal;width:41px;background-color:Yellow;color:#000000;' >CN " + strText + " </td>";
        }
        else
        {
            strTable += "\n<td align='center' style='font-weight:normal;width:41px;background-color:#800000;color:#ffffff;' >Ngày " + strText + " </td>";
        }
        //if (strNameDay != "SATURDAY" && strNameDay != "SUNDAY")
        //{
        //    strTable += "\n<td align='center' style='font-weight:normal;width:41px;background-color:#800000;color:#ffffff;' >Ngày " + strText + " </td>";
        //}
        if (strNameDay == "FRIDAY")
        {
            strTable += strTotal;
        }
    }
    public void loadTruongHoc(ref string strTable, eWorld.UI.CalendarPopup calStart, eWorld.UI.CalendarPopup calEnd)
    {
        int intCheckDate = 0;
        //noi dung cua tung row
        DataSet dsData = objReport.loadReport1bAll(calStart.SelectedDate, calEnd.SelectedDate);
        DataTable dtReport1b = new DataTable();
        if (dsData != null && dsData.Tables[0] != null && dsData.Tables[0].Rows.Count > 0)
        {
            dtReport1b = dsData.Tables[0];
        }
        if (dtReport1b != null && dtReport1b.Rows.Count > 0)
        {
            int intRowi = 0;
            string strColumni = "";
            foreach (string strTruongHoc in strTruongHocs)
            {
                strColumni = strColumn[intRowi];
                intRowi++;
                strTable += "\n<tr>";
                strTable += "\n<td align='left' style='font-weight:bold;' nowrap='nowrap'>" + strTruongHoc + "</td>";
                int intDayTotal = 0;
                int intWeekTotal = 0;
                DateTime dtDatei = calStart.SelectedDate;
                intCheckDate = 0;
                while (dtDatei.Date <= calEnd.SelectedDate.Date)
                {
                    intDayTotal = 0;
                    string strNameDay = dtDatei.DayOfWeek.ToString().ToUpper();
                    string strText = dtDatei.ToString("dd-MM");

                    for (int j = 0; j < dtReport1b.Rows.Count; j++)
                    {
                        DateTime dtTemp = Convert.ToDateTime(dtReport1b.Rows[j]["Ngay"]);
                        if (dtDatei.Date == dtTemp.Date)
                        {
                            intDayTotal += Convert.ToInt32(dtReport1b.Rows[j][strColumni]);
                            intWeekTotal += intDayTotal;
                            intTotalMothThuTrong[intCheckDate] += intDayTotal;
                            break;
                        }
                    }

                    if (strNameDay == "SATURDAY" || strNameDay == "SUNDAY")
                    {
                        //intDayTotal += 1;
                        //intWeekTotal += intDayTotal;
                        //intTotalMothThuTrong[intCheckDate] += intDayTotal;
                        string strData = "&nbsp;";
                        if (intDayTotal > 0)
                        {
                            strData = intDayTotal.ToString();
                        }
                        strTable += "\n<td align='right' style='width:41px;background-color:Yellow;color:#000000;' >" + strData + "</td>";
                    }
                    else
                    {
                        strTable += "\n<td align='right' style='width:41px;' >" + intDayTotal.ToString() + "</td>";
                    }
                    if (strNameDay == "FRIDAY")
                    {
                        strTable += "\n<td align='right' style='width:41px;' >" + intWeekTotal.ToString() + "</td>"; ;
                        intWeekTotal = 0;
                    }
                    intCheckDate++;
                    dtDatei = dtDatei.AddDays(1);
                }
                strTable += "\n</tr>";
            }
        }
    }
    public void loadTotalAllMoth(ref string strTable, eWorld.UI.CalendarPopup calStart, eWorld.UI.CalendarPopup calEnd)
    {
        int intTotal = 0;
        int intCheckDate = 0;
        strTable += "\n<tr>";
        strTable += "\n<td align='left' style='font-weight:bold;color:Red;background-color:#c0c0c0;' nowrap='nowrap'>Tổng </td>";
        DateTime dtDatei = calStart.SelectedDate;
        while (dtDatei.Date <= calEnd.SelectedDate.Date)
        {
            string strNameDay = dtDatei.DayOfWeek.ToString().ToUpper();
            string strText = dtDatei.ToString("dd-MM");
            if (strNameDay == "SATURDAY" || strNameDay == "SUNDAY")
            {
                string strData = "&nbsp;";
                if (intTotalMothThuTrong[intCheckDate] > 0)
                {
                    intTotal += intTotalMothThuTrong[intCheckDate];
                    strData = intTotalMothThuTrong[intCheckDate].ToString();
                }
                strTable += "\n<td align='right' style='width:41px;background-color:Yellow;color:#000000;' >" + strData + "</td>";
            }
            else
            {
                intTotal += intTotalMothThuTrong[intCheckDate];
                strTable += "\n<td align='right' style='width:41px;background-color:#c0c0c0;' >" + intTotalMothThuTrong[intCheckDate].ToString() + "</td>";
            }
            if (strNameDay == "FRIDAY")
            {
                strTable += "\n<td align='right' style='width:41px;background-color:#c0c0c0;' >" + intTotal.ToString() + "</td>"; ;
                intTotal = 0;
            }
            intCheckDate++;
            dtDatei = dtDatei.AddDays(1);
        }
        strTable += "\n</tr>";
    }
    public void insertRow(DataTable dtDataSource)
    {
        string strErro = "";
        foreach (DataRow dr in dtDataSource.Rows)
        {
            DateTime dtmNgay = Convert.ToDateTime(dr["Ngay"].ToString());
            int intL1Nhan = Convert.ToInt32(dr["L1Nhan"]);
            int intKhongLienLac = Convert.ToInt32(dr["KhongLienLac"]);
            int intKhongDungDoiTuong = Convert.ToInt32(dr["KhongDungDoiTuong"]);
            int intVungKhac = Convert.ToInt32(dr["VungKhac"]);
            int intL2Len = Convert.ToInt32(dr["L2Len"]);
            int intKhoaHocID = Convert.ToInt32(dr["KhoaHocID"]);

            int intChẹck = objReport.insertReport1b(dtmNgay, intL1Nhan, intKhongLienLac, intKhongDungDoiTuong, intVungKhac, intL2Len, intKhoaHocID);
            if (intChẹck < 0)
            {
                strErro += dr["ID"].ToString();
            }
        }
    }
    #endregion
    #region "Events"
    protected void Page_Load(object sender, EventArgs e)
    {
        loadDataAccess();
        if (!Page.IsPostBack)
        {
            this.calStartDate.SelectedDate = FirstAndLastDay.GetFirstDayOfMonth(DateTime.Now);
            this.calEndDate.SelectedDate = FirstAndLastDay.GetLastDayOfMonth(DateTime.Now);
            this.calStartDate2.SelectedDate = this.calStartDate.SelectedDate;
            this.calEndDate2.SelectedDate = this.calEndDate.SelectedDate;
            this.calStartDate3.SelectedDate = this.calStartDate.SelectedDate;
            this.calEndDate3.SelectedDate = this.calEndDate.SelectedDate;
            this.calStartDate4.SelectedDate = this.calStartDate.SelectedDate;
            this.calEndDate4.SelectedDate = this.calEndDate.SelectedDate;
        }
    }
    protected void btnReport1b_Click(object sender, EventArgs e)
    {
        this.lblMess.Text = "";
        DateTime dtDatei = this.calStartDate.SelectedDate;
        int intCountDay = 0;
        //Day la lay tieu de
        string strTable = "<table border='1' cellpadding='2' cellspacing='0' align='left' >";
        while (dtDatei.Date <= this.calEndDate.SelectedDate.Date)
        {
            string strNameDay = dtDatei.DayOfWeek.ToString().ToUpper();
            string strText = dtDatei.ToString("dd/MM");
            if (dtDatei == this.calStartDate.SelectedDate)
            {
                strTable += "\n<tr>";
                strTable += "\n<td align='center' style='font-weight:bold;background-color:#800000;color:#ffffff;' nowrap='nowrap'>Đối tượng</td>";
                loadNgayView(ref strTable, strNameDay, strText);
            }
            else if (dtDatei == this.calEndDate.SelectedDate)
            {
                loadNgayView(ref strTable, strNameDay, strText);
                strTable += "\n</tr>";
            }
            else
            {
                loadNgayView(ref strTable, strNameDay, strText);
            }
            intCountDay++;
            dtDatei = dtDatei.AddDays(1);
        }

        intTotalMothThuTrong = new int[intCountDay];
        intTotalMothBanGiao = new int[intCountDay];
        intTotalMothNghiemThu = new int[intCountDay];

        loadTruongHoc(ref strTable, this.calStartDate, this.calEndDate);
        loadTotalAllMoth(ref  strTable, this.calStartDate, this.calEndDate);
        strTable += "\n</table>";
        ltrTable.Text = strTable;
    }
    protected void btnReport1b2_Click(object sender, EventArgs e)
    {
        this.lblMess.Text = "";
        DateTime dtDatei = this.calStartDate2.SelectedDate;
        int intCountDay = 0;
        //Day la lay tieu de
        string strTable = "<table border='1' cellpadding='2' cellspacing='0' align='left' >";
        while (dtDatei.Date <= this.calEndDate2.SelectedDate.Date)
        {
            string strNameDay = dtDatei.DayOfWeek.ToString().ToUpper();
            string strText = dtDatei.ToString("dd/MM");
            if (dtDatei == this.calStartDate2.SelectedDate)
            {
                strTable += "\n<tr>";
                strTable += "\n<td align='center' style='font-weight:bold;background-color:#800000;color:#ffffff;' nowrap='nowrap'>Đối tượng</td>";
                loadNgayView(ref strTable, strNameDay, strText);
            }
            else if (dtDatei == this.calEndDate2.SelectedDate)
            {
                loadNgayView(ref strTable, strNameDay, strText);
                strTable += "\n</tr>";
            }
            else
            {
                loadNgayView(ref strTable, strNameDay, strText);
            }
            intCountDay++;
            dtDatei = dtDatei.AddDays(1);
        }

        intTotalMothThuTrong = new int[intCountDay];
        intTotalMothBanGiao = new int[intCountDay];
        intTotalMothNghiemThu = new int[intCountDay];

        loadTruongHoc(ref strTable, this.calStartDate2, this.calEndDate2);
        loadTotalAllMoth(ref  strTable, this.calStartDate2, this.calEndDate2);
        strTable += "\n</table>";
        ltrTable2.Text = strTable;
    }
    protected void btnReport1b3_Click(object sender, EventArgs e)
    {
        this.lblMess.Text = "";
        DateTime dtDatei = this.calStartDate3.SelectedDate;
        int intCountDay = 0;
        //Day la lay tieu de
        string strTable = "<table border='1' cellpadding='2' cellspacing='0' align='left' >";
        while (dtDatei.Date <= this.calEndDate3.SelectedDate.Date)
        {
            string strNameDay = dtDatei.DayOfWeek.ToString().ToUpper();
            string strText = dtDatei.ToString("dd/MM");
            if (dtDatei == this.calStartDate3.SelectedDate)
            {
                strTable += "\n<tr>";
                strTable += "\n<td align='center' style='font-weight:bold;background-color:#800000;color:#ffffff;' nowrap='nowrap'>Đối tượng</td>";
                loadNgayView(ref strTable, strNameDay, strText);
            }
            else if (dtDatei == this.calEndDate3.SelectedDate)
            {
                loadNgayView(ref strTable, strNameDay, strText);
                strTable += "\n</tr>";
            }
            else
            {
                loadNgayView(ref strTable, strNameDay, strText);
            }
            intCountDay++;
            dtDatei = dtDatei.AddDays(1);
        }

        intTotalMothThuTrong = new int[intCountDay];
        intTotalMothBanGiao = new int[intCountDay];
        intTotalMothNghiemThu = new int[intCountDay];

        loadTruongHoc(ref strTable, this.calStartDate3, this.calEndDate3);
        loadTotalAllMoth(ref  strTable, this.calStartDate3, this.calEndDate3);
        strTable += "\n</table>";
        ltrTable3.Text = strTable;
    }
    protected void btnReport1b4_Click(object sender, EventArgs e)
    {
        this.lblMess.Text = "";
        DateTime dtDatei = this.calStartDate4.SelectedDate;
        int intCountDay = 0;
        //Day la lay tieu de
        string strTable = "<table border='1' cellpadding='2' cellspacing='0' align='left' >";
        while (dtDatei.Date <= this.calEndDate4.SelectedDate.Date)
        {
            string strNameDay = dtDatei.DayOfWeek.ToString().ToUpper();
            string strText = dtDatei.ToString("dd/MM");
            if (dtDatei == this.calStartDate4.SelectedDate)
            {
                strTable += "\n<tr>";
                strTable += "\n<td align='center' style='font-weight:bold;background-color:#800000;color:#ffffff;' nowrap='nowrap'>Đối tượng</td>";
                loadNgayView(ref strTable, strNameDay, strText);
            }
            else if (dtDatei == this.calEndDate4.SelectedDate)
            {
                loadNgayView(ref strTable, strNameDay, strText);
                strTable += "\n</tr>";
            }
            else
            {
                loadNgayView(ref strTable, strNameDay, strText);
            }
            intCountDay++;
            dtDatei = dtDatei.AddDays(1);
        }

        intTotalMothThuTrong = new int[intCountDay];
        intTotalMothBanGiao = new int[intCountDay];
        intTotalMothNghiemThu = new int[intCountDay];

        loadTruongHoc(ref strTable, this.calStartDate4, this.calEndDate4);
        loadTotalAllMoth(ref strTable, this.calStartDate4, this.calEndDate4);
        strTable += "\n</table>";
        ltrTable4.Text = strTable;
    }
    protected void btnInportExcel_Click(object sender, EventArgs e)
    {
        try
        {
            SqlConnection sqlConnec = ConnectData.getConnection();
            String filename = fileInportExcel.FileName.ToString();
            ExcelHelper.uploadXLFile(fileInportExcel, Server.MapPath("~/OutputFiles/"));
            String[] sheetNames = new String[100];
            sheetNames = ExcelHelper.getExcelSheets(Server.MapPath("~/OutputFiles/") + filename);
            // loop through worksheet names
            foreach (string shtname in sheetNames)
            {
                DataSet ds = new DataSet();
                if (shtname.Equals("Report1b"))
                {
                    ds = ExcelHelper.getXLData(shtname, Server.MapPath("~/OutputFiles/") + filename, "");
                    if (ds != null)
                    {
                        insertRow(ds.Tables[0]);
                    }
                }
            }
            this.lblMess.Text = "Đồng bộ dữ liệu thành công";
        }
        catch (Exception ex)
        {
            this.lblMess.Text = "Không đồng bộ dữ liệu được: " + ex.Message;
        }
    }
    #endregion
}